package com.lanou.dao;

import com.lanou.pojo.*;
import com.lanou.utils.DBCPUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class SelectDao {
//    Connection conn = DBCPUtil.getConnection();
//    QueryRunner qr = new QueryRunner();
    //登录
    public Tb_user selectByusername(String username, String password) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Tb_user tbUser = qr.query(conn,"select * from tb_user where username=? and password=?",
                new BeanHandler<>(Tb_user.class),username,password);
        return tbUser;
    }
    //查询种类
    public List selectCategory() throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        List<Category> catelist = qr.query(conn, "select * from category",
                new BeanListHandler<>(Category.class));
        return catelist;
    }
    //查询单个种类
    public String selectCateById(Integer cid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Category cname = qr.query(conn, "select * from category where cid=?",
                new BeanHandler<>(Category.class),cid);
        return cname.getCname();
    }
    //查找所有图书
    public List<Book> selectBook() throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        List<Book> book = qr.query(conn, "select * from book",
                new BeanListHandler<>(Book.class));
        return book;
    }
    //通过id查用户名
    public String selectUserByid(int id) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        String username = qr.query(conn, "select username from tb_user where uid=?",
                new ScalarHandler<>(), id);
        return username;
    }
    //查询特定种类图书数量
    public Long selectBookBycid(int cid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Long bookcount = qr.query(conn, "select count(*) from book where cid=?",
                new ScalarHandler<>(), cid);
        return bookcount;
    }
    //分页查询图书
    public List<Book> selectBookLimit(int cid, int catepage) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        List<Book> bookLimit = qr.query(conn, "select * from book where cid=? limit ?,8",
                new BeanListHandler<>(Book.class), cid, catepage);
        return bookLimit;
    }
//  通过bid查询图书
    public Book selectBookBybid(int bid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Book book = qr.query(conn, "select * from book where bid=?",
                new BeanHandler<>(Book.class), bid);
        return book;
    }
    //通过用户id查询购物车
    public List<Car> selectCarByuid(int uid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        List<Car> car = qr.query(conn, "select * from car where uid=?",
                new BeanListHandler<>(Car.class), uid);
        return car;
    }
    //通过UID查找地址
    public String selectAddressBYuid(int uid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        String address = qr.query(conn, "select address from tb_user where uid=?",
                new ScalarHandler<>(), uid);
        return address;
    }
    //查询购物车是否为空
    public Long selectCarcount(int uid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Long carcount = qr.query(conn, "select count(*) from car where uid=?",
                new ScalarHandler<>(), uid);
//        conn.commit();
        return carcount;
    }

    public Long selectCarcountagain(int uid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Long carcount1 = qr.query(conn, "select count(*) from car where uid=?",
                new ScalarHandler<>(), uid);
//        conn.commit();
        return carcount1;
    }
//  通过UID查找
    public List<Orders> selectOrdersByuid(int uid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        List<Orders> orders = qr.query(conn, "select * from orders where uid=?",
                new BeanListHandler<>(Orders.class), uid);
        return orders;
    }
//  通过UID找用户信息
    public Tb_user selectAllUserByuid(int uid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        Tb_user tbUser = qr.query(conn, "select * from tb_user where uid=?",
                new BeanHandler<>(Tb_user.class), uid);
        return tbUser;
    }
//  通过oid查询订单详情
    public List<Orderitem> selectorderitemByoid(int oid) throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        List<Orderitem> orderitems = qr.query(conn, "select * from orderitem where oid=?",
                new BeanListHandler<>(Orderitem.class), oid);
        return orderitems;
    }
//  查找所有订单
    public List<Orders> selectOrders() throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        List<Orders> orders = qr.query(conn, "select * from orders",
                new BeanListHandler<>(Orders.class));
        return orders;
    }
//  查找所有订单详情
    public List<Orderitem> selectOrderitem() throws SQLException {
        Connection conn = DBCPUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        List<Orderitem> orderitem = qr.query(conn, "select * from orderitem",
                new BeanListHandler<>(Orderitem.class));
        return orderitem;
    }
}
